package co.com.inventarios.persistencia;


import java.util.List;
import java.util.logging.Level;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import co.com.inventarios.modelo.Mngmcn;


public class QueryMngmcnDAO {

	private EntityManager getEntityManager() {
		return EntityManagerHelper.getEntityManager();
	}

	IMngmcnDAO Mngmcn_DAO = new MngmcnDAO();

	public List<Mngmcn> Buscar_Detalle(String empresa, String clase,
			String tipo, Double numero) {
		try {
			//getEntityManager().clear();
			final String queryString = "select model from Mngmcn model where model.id.mcnvinkey ='.' AND model.mcnindinv ='E' AND model.id.mcnempresa='"
					+ empresa
					+ "' AND model.id.mcnclase='"
					+ clase
					+ "' AND model.id.mcntipodoc='"
					+ tipo
					+ " "
					+ "' AND model.id.mcnnumedoc="
					+ numero
					+ " "
					+ " order by model.id.mcnreg DESC";
			Query query = getEntityManager().createQuery(queryString);
			
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed Mngmcn",
					Level.SEVERE, re);
			throw re;
		}
	}
	
	
	public List<Mngmcn> Buscar_Detalle_All(String empresa, String clase,
			String tipo, Double numero) {
		try {
			//getEntityManager().clear();
			final String queryString = "select model from Mngmcn model where model.id.mcnvinkey ='.' AND model.id.mcnempresa='"
					+ empresa
					+ "' AND model.id.mcnclase='"
					+ clase
					+ "' AND model.id.mcntipodoc='"
					+ tipo
					+ " "
					+ "' AND model.id.mcnnumedoc="
					+ numero
					+ " "
					+ " order by model.id.mcnreg DESC";
			Query query = getEntityManager().createQuery(queryString);
			
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed Mngmcn",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<Mngmcn> buscar_inv_Kardexw(String empresa,String bodega, String producto,
			String fechabusca,String fechabusca_fin, String propiedad,
			String valor) {
		try {
			getEntityManager().clear();
			String queryString = "";
			if (propiedad == ".") {
				       queryString = "select model from Mngmcn model where model.id.mcnvinkey ='.' AND model.id.mcnempresa='"
						+ empresa
						+ "' AND model.producto.procodigo ='"
						+ producto
						+ "' AND  model.mngdoc.docfecha  >= '"
						+ fechabusca
						+ "'  AND  model.bodega.bodcodigo='"+bodega+"' AND model.mngdoc.label.lbcodnum = 8  "
						+ " order by model.mngdoc.docfecha DESC";
			} else {

				     queryString = "select model from Mngmcn model where model.id.mcnvinkey ='.' AND model.id.mcnempresa='"
						+ empresa
						+ "' AND model.producto.procodigo ='"
						+ producto
						+ "' AND  model.mngdoc.docfecha  >= '"
						+ fechabusca
						+ "' AND  "+propiedad
						+ " AND  model.bodega.bodcodigo='"+bodega+"' AND model.mngdoc.label.lbcodnum = 8  "
						+ " order by model.mngdoc.docfecha DESC";
			}

			Query query = getEntityManager().createQuery(queryString);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed Mngmcn",
					Level.SEVERE, re);
			throw re;
		}
	}

	
	public Double buscar_inv_Kardex_ini(String empresa,String bodega, String producto,
			String fechabusca) {
		try {
			getEntityManager().clear();
			String queryString = "";
			String queryStrings = "";

				       queryString = "select sum(model.mcncantid) from Mngmcn model where model.id.mcnvinkey ='.'AND model.mcnindinv ='E' AND model.id.mcnempresa='"
						+ empresa
						+ "' AND model.producto.procodigo ='"
						+ producto
						+ "' AND  model.mngdoc.docfecha >= '"
						+ fechabusca
						+ "' AND model.bodega.bodcodigo='"+bodega+"'  AND model.mngdoc.label.lbcodnum = 8 "
						+ " order by model.mngdoc.docfecha DESC";

				       queryStrings = "select sum(model.mcncantid) from Mngmcn model where model.id.mcnvinkey ='.'AND model.mcnindinv ='S' AND model.id.mcnempresa='"
							+ empresa
							+ "' AND model.producto.procodigo ='"
							+ producto
							+ "' AND  model.mngdoc.docfecha >= '"
							+ fechabusca
							+ "' AND model.bodega.bodcodigo='"+bodega+"'  AND model.mngdoc.label.lbcodnum = 8  "
							+ " order by model.mngdoc.docfecha DESC";
						Query query = getEntityManager().createQuery(queryString);
						List list = query.getResultList();
						Double Ent = (Double) list.get(0);
						if(Ent == null){
							Ent = Double.valueOf(0);	
						}
						System.out.println("wwwwwwMax Invested Amount CANTIDAD ENTRA: " + Ent);
						
						Query querys = getEntityManager().createQuery(queryStrings);
						List lists = querys.getResultList();
						Double Sal = (Double) lists.get(0);
						if(Sal == null){
							Sal = Double.valueOf(0);	
						}
						System.out.println("wwwwwwMax Invested Amount CANTIDAD SALE: " + Sal);
						
						Double total = (Ent - Sal);
						if(total == null){
						   total = Double.valueOf(0);	
						}
						//System.out.println("wwwwwwMax Invested Amount: " + total);

			return total;
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed Mngmcn",
					Level.SEVERE, re);
			throw re;
		}
	}
	
	public Double MaxId(String empresa, String clase, String tipo, Double numero) {
		EntityManagerHelper.log("finding Mngmcn instance with id: " + clase,
				Level.INFO, null);
		try {
			getEntityManager().clear();
			final String queryString = "select max(model.id.mcnreg) from Mngmcn model where model.id.mcnempresa='"
					+ empresa
					+ "' AND model.id.mcnclase='"
					+ clase
					+ "' AND model.id.mcntipodoc='"
					+ tipo
					+ "' AND model.id.mcnnumedoc=" + numero + " ";
			Query query = getEntityManager().createQuery(queryString);
			List list = query.getResultList();
			// System.out.println("Max Invested Amount: " + list.get(0));

			return (Double) list.get(0);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

}
